﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAL
{
    using System.Data;
    using Entities;
    public class NhanvienDAL: Base, Ibase
    {
        public int count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            var sql = "SELECT * FROM NHAN_VIEN";
            return ExecuteQuery(sql);
        }

        public object Select(string id)
        {
            try
            {
                var sql = "SELECT * FROM NHAN_VIEN WHERE MA_NV = '{0}'";
                sql = String.Format(sql, id);
                var tmp = ExecuteQuery(sql);

                if (tmp != null & tmp.Rows.Count > 0)
                {
                    var dg = new Nhanvien();
                    dg.MA_NV = tmp.Rows[0]["MA_NV"] + "";
                    dg.HOTEN_NV = tmp.Rows[0]["HOTEN_NV"] + "";
                    dg.TEN_DANG_NHAP = tmp.Rows[0]["TEN_DANG_NHAP"] + "";
                    dg.MA_CV = tmp.Rows[0]["MA_CV"] + "";
                    dg.GIOI_TINH = tmp.Rows[0]["GIOI_TINH"] + "";
                    dg.DIA_CHI = tmp.Rows[0]["DIA_CHI"] + "";
                    dg.SO_DT = tmp.Rows[0]["SO_DT"] + "";
                    dg.MUC_LUONG = tmp.Rows[0]["MUC_LUONG"] + "";


                    return dg;
                }
                else return null;
            }
            catch { return null; }
        }

        public int Update(object obj)
        {
            try
            {
                var o = (Nhanvien)obj;
                var sql = "UPDATE NHAN_VIEN SET HOTEN_NV=N'{1}', TEN_DANG_NHAP='{2}', MA_CV='{3}',GIOI_TINH =N'{4}',  DIA_CHI=N'{5}', SO_DT= '{6}', MUC_LUONG= '{7}' WHERE MA_NV = '{0}'";
                sql = string.Format(sql, o.MA_NV, o.HOTEN_NV, o.TEN_DANG_NHAP, o.MA_CV, o.GIOI_TINH, o.DIA_CHI, o.SO_DT, o.MUC_LUONG);
                return ExecuteNonQuery(sql);
            }
            catch
            {
                return -1;
            }
        }

        public int Insert(object obj)
        {
            try
            {
                var o = (Nhanvien)obj;
                var sql = "INSERT INTO NHAN_VIEN(MA_NV, HOTEN_NV, TEN_DANG_NHAP, MA_CV,GIOI_TINH,  DIA_CHI, SO_DT, MUC_LUONG) VALUES ('{0}', N'{1}', N'{2}', N'{3}', N'{4}', N'{5}', N'{6}', N'{7}')";
                sql = string.Format(sql, o.MA_NV, o.HOTEN_NV, o.TEN_DANG_NHAP, o.MA_CV, o.GIOI_TINH, o.DIA_CHI, o.SO_DT, o.MUC_LUONG);
                return ExecuteNonQuery(sql);
            }
            catch
            {
                return -1;
            }
        }

        public int Delete(string id)
        {
            try
            {

                var sql = "DELETE FROM NHAN_VIEN WHERE MA_NV = '{0}'";
                sql = string.Format(sql, id);
                return ExecuteNonQuery(sql);
            }
            catch
            {
                return -1;
            }
        }
        public DataTable Select_MA_NV(string ten)
        {
            var sql = "SELECT * FROM NHAN_VIEN WHERE MA_NV LIKE N'%" + ten + "%'";
            return ExecuteQuery(sql);
        }
        public DataTable Select_HOTEN_NV(string ten1)
        {
            var sql = "SELECT * FROM NHAN_VIEN WHERE HOTEN_NV LIKE N'%" + ten1 + "%'";
            return ExecuteQuery(sql);
        }

        public DataTable Select_DIA_CHI(string ten2)
        {
            var sql = "SELECT * FROM NHAN_VIEN WHERE DIA_CHI LIKE N'%" + ten2 + "%'";
            return ExecuteQuery(sql);
        }
        public DataTable Select_CHUC_VU(string ten3)
        {
            var sql = "SELECT Z.MA_NV, Z.HOTEN_NV, Z.TEN_DANG_NHAP, Z.MA_CV,Z.GIOI_TINH,  Z.DIA_CHI, Z.SO_DT, Z.MUC_LUONG FROM NHAN_VIEN Z, CHUC_VU Y WHERE  Z.MA_CV=Y.MA_CV AND Z.MA_CV LIKE N'%" + ten3 + "%'";
            return ExecuteQuery(sql);
        }
    }
}
